package com.infodms.dms.dao.sales.customerInfoManage;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.dao.sales.storageManage.CheckVehicleDAO;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;

public class SalesReportDAO extends BaseDao {
	public static Logger logger = Logger.getLogger(SalesReportDAO.class);
	private static POFactory factory = POFactoryBuilder.getInstance();
	private static final CheckVehicleDAO dao = new CheckVehicleDAO();

	public static final CheckVehicleDAO getInstance() {
		return dao;
	}

	/***
	 *查询可"实销上报"的车辆列表
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> getCanReportVehiclet(
			String areaId,String dealer_Id, String materialCode, String vin, int pageSize,
			int curPage) {
		StringBuffer sql = new StringBuffer("");

		sql.append("SELECT TMV.VEHICLE_ID,\n");
		sql.append("       TO_CHAR(TMV.PRODUCT_DATE,'yyyy-MM-dd') PRODUCT_DATE, --生产日期\n");
		sql.append("       TMVM.MATERIAL_CODE, --物料代码\n");
		sql.append("       G.GROUP_NAME PACKAGE_NAME, --配置\n");
		sql.append("       TMVM.COLOR_NAME COLOR, --颜色\n");
		sql.append("       TMV.VIN, --VIN\n");
		sql.append("       TMV.ENGINE_NO, --VIN\n");
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') STORAGE_DATE --验收入库日期\n");
		sql.append("  FROM TM_VEHICLE TMV, TM_VHCL_MATERIAL TMVM, TM_VHCL_MATERIAL_GROUP G\n");
		sql.append(" WHERE TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");
		sql.append("   AND TMV.PACKAGE_ID = G.GROUP_ID\n");
		sql.append("   AND TMV.LIFE_CYCLE = " + Constant.VEHICLE_LIFE_03+ "\n");
		sql.append("   AND TMV.LOCK_STATUS = "+Constant.LOCK_STATUS_01+"\n");  
		sql.append("   AND TMV.DEALER_ID IN (" + dealer_Id + ")\n");
		if (null != areaId && !"".equals(areaId)) {
			sql.append("   AND TMV.AREA_ID IN ("+areaId+")");
		}
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'")
							.append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                              WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + "))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                            WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + "))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                             WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + ")))) \n");
			}

		}

		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin, "TMV"));
		}
		sql.append(" ORDER BY TMV.STORAGE_DATE DESC,TMV.VEHICLE_ID\n");

		return dao
				.pageQuery(
						sql.toString(),
						null,
						"com.infodms.dms.dao.sales.customerInfoManage.SalesReportDAO.getCanReportVehiclet",
						pageSize, curPage);
	}

	/***
	 *实销信息上报:填写上报信息--查看车辆信息
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> getVehicleInfo(String vehicleId) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT TMV.VEHICLE_ID,                         \n");
		sql.append("	   TMV.VIN,                          --VIN \n");
		sql.append("       TMV.ENGINE_NO,                    --发动机号 \n");
		sql.append("       G1.GROUP_NAME SERIES_NAME,        --车系 \n");
		sql.append("       G2.GROUP_NAME MODEL_NAME,         --车型 \n");
		sql.append("       TMVM.MATERIAL_CODE,               --物料代码 \n");
		sql.append("       TMVM.MATERIAL_NAME,               --物料名称 \n");
		sql.append("       TMVM.COLOR_NAME AS COLOR,                        --颜色 \n");
		sql.append("       SALES.IS_OLD_CTM,                 --新/老客户 \n");
		sql.append("       SALES.CAR_CHARACTOR,              --车辆性质 \n");
		sql.append("       SALES.ORDER_ID,                   --实销ID \n");
		sql.append("       G2.GROUP_ID,                       --车型id\n");
		sql.append("       SALES.ORDER_ID");
		sql.append("  FROM TM_VEHICLE TMV, \n");
		sql.append("       TM_VHCL_MATERIAL_GROUP G1, \n");
		sql.append("       TM_VHCL_MATERIAL_GROUP G2, \n");
		sql.append("       TM_VHCL_MATERIAL TMVM, \n");
		sql.append("       TT_DEALER_ACTUAL_SALES SALES \n");
		sql.append(" WHERE     TMV.SERIES_ID = G1.GROUP_ID \n");
		sql.append("       AND TMV.MODEL_ID = G2.GROUP_ID \n");
		sql.append("       AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID \n");
		sql.append("	   AND TMV.VEHICLE_ID = SALES.VEHICLE_ID(+)\n");
		sql.append("       AND TMV.VEHICLE_ID = " + vehicleId + " \n");
		sql.append("       ORDER BY SALES.ORDER_ID DESC ");
		//sql.append("       AND SALES.IS_RETURN='"+Constant.IF_TYPE_NO+"'\n");
		return dao.pageQueryMap(sql.toString(), null, dao.getFunName());
	}
	
	public static List<Map<String, Object>> getDealerCalDetailExportList(Map map,String dealerId) {
		String customer_type = (String) map.get("customer_type"); // 客户类型
		String customer_name = (String) map.get("customer_name"); // 客户名称
		String vin = (String) map.get("vin"); // VIN
		String customer_phone = (String) map.get("customer_phone"); // 客户电话
		String materialCode = (String) map.get("materialCode"); // 选择物料组
		String is_fleet = (String) map.get("is_fleet"); // 是否大客户
		String fleet_name = (String) map.get("fleet_name"); // 大客户名称
		String contract_no = (String) map.get("contract_no"); // 大客户合同
		String startDate = (String) map.get("startDate"); // 上报日期:开始
		String endDate = (String) map.get("endDate"); // 上报日期 ：截止
		String areaId=(String)map.get("areaId");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_NAME, TTC.CTM_NAME) C_NAME, --客户名称\n");
		sql.append("       TMD.DEALER_SHORTNAME, --经销商简称\n"); 
		sql.append("       TC1.CODE_DESC CTM_TYPE, --客户类型\n"); 
		sql.append("       TTC.ADDRESS, --联系人地址\n"); 
		sql.append("       TC2.CODE_DESC IS_FLEET, --是否大客户\n");
		sql.append("       DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_PHONE, TTC.MAIN_PHONE) MAIN_PHONE, --主要联系电话\n");
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMVM.MATERIAL_NAME,\n");
		sql.append("       TMV.VIN,\n");
		sql.append("       TMV.ENGINE_NO, --VIN\n");
		sql.append("       TO_CHAR(TTS.SALES_DATE, 'yyyy-MM-dd') SALES_DATE, --实销时间\n");
		sql.append("       TO_CHAR(TMV.PRODUCT_DATE, 'yyyy-MM-dd') PRODUCT_DATE, --生产日期\n");
		sql.append("       TMV.VEHICLE_ID,\n");
		sql.append("       TTC.CTM_ID\n");
		sql.append("  FROM TT_DEALER_ACTUAL_SALES TTS,\n");
		sql.append("       TT_CUSTOMER            TTC,\n");
		sql.append("       TM_VEHICLE             TMV,\n");
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");
		sql.append("       TM_DEALER              TMD,\n");
		sql.append("       TC_CODE       		  TC1,\n");
		sql.append("       TC_CODE       		  TC2\n");
		sql.append(" WHERE TTS.CTM_ID = TTC.CTM_ID\n");
		sql.append("   AND TTS.VEHICLE_ID = TMV.VEHICLE_ID\n");
		sql.append("   AND TTC.CTM_TYPE = TC1.CODE_ID\n");
		sql.append("   AND TTS.IS_FLEET = TC2.CODE_ID\n");
		sql.append("   AND TTS.DEALER_ID = TMD.DEALER_ID\n");
		sql.append("   AND  TTS.IS_RETURN ="+Constant.IF_TYPE_NO+"\n");
		if(areaId!=null && areaId!=""){
			sql.append("   AND TMV.AREA_ID='"+areaId+"'");
		}
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");
		sql.append("   AND TMV.LIFE_CYCLE = " + Constant.VEHICLE_LIFE_04
						+ "\n");
		sql.append("   AND TMV.DEALER_ID IN (" + dealerId + ")\n");
		// 客户类型
		if (null != customer_type && !"".equals(customer_type) && !"-1".equals(customer_type)) {
			sql.append("   AND TTC.CTM_TYPE =" + customer_type + "\n");
		}
		// 客户名称
		if (null != customer_name && !"".equals(customer_name)) {
			sql.append("   AND (TTC.CTM_NAME LIKE '%" + customer_name.trim() + "%' OR TTC.COMPANY_S_NAME LIKE '%" + customer_name.trim() + "%')\n");
		}
		// VIN
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin, "TMV"));
		}
		// 客户电话
		if (null != customer_phone && !"".equals(customer_phone)) {
			sql.append("   AND (TTC.MAIN_PHONE LIKE '%" + customer_phone.trim() + "%' OR TTC.COMPANY_PHONE LIKE '%" + customer_phone.trim() + "%')\n");
		}
		// 选择物料组
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                              WHERE G.GROUP_CODE IN (" + buffer.toString() + "))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                            WHERE G.GROUP_CODE IN (" + buffer.toString() + "))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                             WHERE G.GROUP_CODE IN (" + buffer.toString() + ")))) \n");
			}
		}
		// 是否大客户
		if (null != is_fleet && !"".equals(is_fleet) && !"-1".equals(is_fleet)) {
			sql.append("   AND TTS.IS_FLEET = " + is_fleet + "\n");
		}
		if (!is_fleet.equals(Constant.IF_TYPE_NO.toString())) {
			// 大客户名称
			if (null != fleet_name && !"".equals(fleet_name)) {
				sql.append("   AND TTC.COMPANY_S_NAME LIKE '%" + fleet_name.trim() + "%'\n");
			}
			// 大客户合同
			if (null != contract_no && !"".equals(contract_no)) {
				sql.append("   AND TTS.CONTRACT_NO LIKE '%" + contract_no.trim() + "%'\n\n");
			}
		}
		// 上报日期
		if (null != startDate && !"".equals(startDate)) {
			sql.append("   AND TTS.SALES_DATE >= TO_DATE('" + startDate + "','yyyy-MM-dd')\n");
		}
		if (null != endDate && !"".equals(endDate)) {
			sql.append("   AND TTS.SALES_DATE  <= (TO_DATE('" + endDate + "','yyyy-MM-dd')+1)\n");
		}
		sql.append(" ORDER BY TTS.SALES_DATE DESC\n");
		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return  list;
	}

	/***
	 *实销信息查询:查询实销历史信息
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> queryReportInfoList(String areaId,
			String dealerId, Map map, int pageSize, int curPage) {

		String customer_type = (String) map.get("customer_type"); // 客户类型
		String customer_name = (String) map.get("customer_name"); // 客户名称
		String vin = (String) map.get("vin"); // VIN
		String customer_phone = (String) map.get("customer_phone"); // 客户电话
		String materialCode = (String) map.get("materialCode"); // 选择物料组
		String is_fleet = (String) map.get("is_fleet"); // 是否集团客户
		String fleet_name = (String) map.get("fleet_name"); // 集团客户名称
		String contract_no = (String) map.get("contract_no"); // 集团客户合同
		String startDate = (String) map.get("startDate"); // 上报日期:开始
		String endDate = (String) map.get("endDate"); // 上报日期 ：截止

		StringBuffer sql = new StringBuffer("\n");
		
		sql.append("SELECT DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_NAME, TTC.CTM_NAME) C_NAME, --客户名称\n");
		sql.append("       TMD.DEALER_SHORTNAME, --经销商简称\n");
		sql.append("       TTC.CTM_TYPE, --客户类型\n");
		sql.append("       TTS.IS_FLEET, --是否集团客户\n");
		sql.append("       DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_PHONE, TTC.MAIN_PHONE) MAIN_PHONE, --主要联系电话\n");
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMVM.MATERIAL_NAME,\n");
		sql.append("       TMV.VIN,\n");
		sql.append("       TMV.ENGINE_NO, --VIN\n");
		sql.append("		TTS.CAR_CHARACTOR, \n") ;
		sql.append("       TO_CHAR(TTS.SALES_DATE, 'yyyy-MM-dd') SALES_DATE, --实销时间\n");
		sql.append("       TMV.VEHICLE_ID,\n");
		sql.append("       TO_CHAR(TMV.PRODUCT_DATE,'yyyy-MM-dd') PRODUCT_DATE, --生产日期\n");
		sql.append("       TTC.CTM_ID\n");
		sql.append("  FROM TT_DEALER_ACTUAL_SALES TTS,\n");
		sql.append("       TT_CUSTOMER            TTC,\n");
		sql.append("       TM_VEHICLE             TMV,\n");
		sql.append("       TM_DEALER       		  TMD,\n");
		sql.append("       TM_VHCL_MATERIAL       TMVM\n");
		sql.append(" WHERE TTS.CTM_ID = TTC.CTM_ID\n");
		sql.append("   AND TTS.VEHICLE_ID = TMV.VEHICLE_ID\n");
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");
		sql.append("   AND TTS.DEALER_ID = TMD.DEALER_ID\n");
		sql.append("   AND TTS.IS_RETURN ="+Constant.IF_TYPE_NO+"\n");
		sql.append("   AND TMV.LIFE_CYCLE = " + Constant.VEHICLE_LIFE_04 + "\n");
		sql.append("   AND TMV.DEALER_ID IN (" + dealerId + ")\n");
		// 客户类型
		if (null != customer_type && !"".equals(customer_type)
				&& !"-1".equals(customer_type)) {
			sql.append("   AND TTC.CTM_TYPE =" + customer_type + "\n");
		}
		if(areaId!="" && areaId!=null){
			sql.append("   AND TMV.AREA_ID IN ("+areaId+")\n");
		}
		// 客户名称
		if (null != customer_name && !"".equals(customer_name)) {
			sql.append("   AND (TTC.CTM_NAME LIKE '%" + customer_name.trim()
					+ "%' OR TTC.COMPANY_S_NAME LIKE '%" + customer_name.trim()
					+ "%')\n");
		}
		// VIN
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin, "TMV"));
		}
		// 客户电话
		if (null != customer_phone && !"".equals(customer_phone)) {
			sql.append("   AND (TTC.MAIN_PHONE LIKE '%" + customer_phone.trim()
					+ "%' OR TTC.COMPANY_PHONE LIKE '%" + customer_phone.trim()
					+ "%')\n");
		}
		// 选择物料组
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'")
							.append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                              WHERE G.GROUP_CODE IN (" + buffer.toString() + "))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                            WHERE G.GROUP_CODE IN (" + buffer.toString() + "))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                             WHERE G.GROUP_CODE IN (" + buffer.toString() + ")))) \n");
			}
		}
		// 是否集团客户
		if (null != is_fleet && !"".equals(is_fleet) && !"-1".equals(is_fleet)) {
			sql.append("   AND TTS.IS_FLEET = " + is_fleet + "\n");
		}
		if (!is_fleet.equals(Constant.IF_TYPE_NO.toString())) {
			// 集团客户名称
			if (null != fleet_name && !"".equals(fleet_name)) {
				sql.append("   AND TTC.CTM_NAME LIKE '%"
						+ fleet_name.trim() + "%'\n");
			}
			// 集团客户合同
			if (null != contract_no && !"".equals(contract_no)) {
				sql.append("   AND TTS.CONTRACT_NO LIKE '%"
						+ contract_no.trim() + "%'\n\n");
			}
		}
		// 上报日期
		if (null != startDate && !"".equals(startDate)) {
			sql.append("   AND TTS.SALES_DATE >= TO_DATE('" + startDate
					+ "','yyyy-MM-dd')\n");
		}
		if (null != endDate && !"".equals(endDate)) {
			sql.append("   AND TTS.SALES_DATE  <= (TO_DATE('" + endDate
					+ "','yyyy-MM-dd')+1)\n");
		}
		sql.append(" ORDER BY TTS.SALES_DATE DESC\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}

	public static List getPartentCompany(String dealers) {
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT TMD.COMPANY_ID FROM TM_DEALER TMD WHERE TMD.DEALER_ID IN(\n");
		sql.append(" SELECT VOD1.DEALER_ID\n");
		sql.append(" FROM VW_ORG_DEALER VOD1\n");
		sql.append(" WHERE VOD1.ROOT_DEALER_ID IN\n");
		sql.append(" (SELECT VOD.ROOT_DEALER_ID\n");
		sql.append(" FROM VW_ORG_DEALER VOD\n");
		sql.append(" WHERE VOD.DEALER_ID = "+dealers+"))\n");
		
		List list=factory.select(sql.toString(), null,new DAOCallback(){
			List mylist=new ArrayList();
			public List wrapper(ResultSet rs, int idx){
				int count=0;
				try{
					count=rs.getInt("COMPANY_ID");
				}catch(Exception e){
					e.printStackTrace();
				}
				mylist.add(count);
				return mylist;	
			}
		});
		return list;
	}
	
	/***
	 *实销信息更改申请:查询可申请更改的信息
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> queryReportInfoList_Change(String areaId,
			String dealerId, Map map, int pageSize, int curPage) {

		String customer_type = (String) map.get("customer_type"); // 客户类型
		String customer_name = (String) map.get("customer_name"); // 客户名称
		String vin = (String) map.get("vin"); // VIN
		String customer_phone = (String) map.get("customer_phone"); // 客户电话
		String materialCode = (String) map.get("materialCode"); // 选择物料组
		String is_fleet = (String) map.get("is_fleet"); // 是否集团客户
		String fleet_name = (String) map.get("fleet_name"); // 集团客户名称
		String contract_no = (String) map.get("contract_no"); // 集团客户合同
		String startDate = (String) map.get("startDate"); // 上报日期:开始
		String endDate = (String) map.get("endDate"); // 上报日期 ：截止

		StringBuffer sql = new StringBuffer();
		sql
				.append("SELECT DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_NAME, TTC.CTM_NAME) C_NAME, --客户名称\n");
		sql.append("       TTC.CTM_TYPE, --客户类型\n");
		sql.append("       TTS.IS_FLEET, --是否集团客户\n");
		sql
				.append("       DECODE(TTC.CTM_NAME, NULL, TTC.COMPANY_PHONE, TTC.MAIN_PHONE) MAIN_PHONE, --主要联系电话\n");
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMVM.MATERIAL_NAME,\n");
		sql.append("       TMV.VIN,\n");
		sql
				.append("       TO_CHAR(TTS.SALES_DATE, 'yyyy-MM-dd') SALES_DATE, --实销时间\n");
		sql.append("       TMV.VEHICLE_ID,\n");
		sql.append("       TTC.CTM_ID\n");
		sql.append("  FROM TT_DEALER_ACTUAL_SALES TTS,\n");
		sql.append("       TT_CUSTOMER            TTC,\n");
		sql.append("       TM_VEHICLE             TMV,\n");
		sql.append("       TM_VHCL_MATERIAL       TMVM\n");
		sql.append(" WHERE TTS.CTM_ID = TTC.CTM_ID\n");
		sql.append("   AND TTS.VEHICLE_ID = TMV.VEHICLE_ID\n");
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");
		sql.append("   AND TTS.IS_RETURN!='"+Constant.IF_TYPE_YES+"'\n");
		if(areaId!=null&&areaId!=""){
			sql.append("   AND TMV.AREA_ID IN ("+areaId+")\n");
		}
		sql.append("   AND TMV.LIFE_CYCLE = " + Constant.VEHICLE_LIFE_04
						+ "\n");
		sql.append("   AND TMV.DEALER_ID IN (" + dealerId + ")\n");
		// 客户类型
		if (null != customer_type && !"".equals(customer_type)
				&& !"-1".equals(customer_type)) {
			sql.append("   AND TTC.CTM_TYPE =" + customer_type + "\n");
		}
		// 客户名称
		if (null != customer_name && !"".equals(customer_name)) {
			sql.append("   AND (TTC.CTM_NAME LIKE '%" + customer_name.trim()
					+ "%' OR TTC.COMPANY_S_NAME LIKE '%" + customer_name.trim()
					+ "%')\n");
		}
		// VIN
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin, "TMV"));
		}
		// 客户电话
		if (null != customer_phone && !"".equals(customer_phone)) {
			sql.append("   AND (TTC.MAIN_PHONE LIKE '%" + customer_phone.trim()
					+ "%' OR TTC.COMPANY_PHONE LIKE '%" + customer_phone.trim()
					+ "%')\n");
		}
		// 选择物料组
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length > 0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'")
							.append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length() - 1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                              WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + "))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                            WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + "))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql
						.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql
						.append("                             WHERE G.GROUP_CODE IN ("
								+ buffer.toString() + ")))) \n");
			}
		}
		// 是否集团客户
		if (null != is_fleet && !"".equals(is_fleet) && !"-1".equals(is_fleet)) {
			sql.append("   AND TTS.IS_FLEET = " + is_fleet + "\n");
		}
		if (!is_fleet.equals(Constant.IF_TYPE_NO.toString())) {
			// 集团客户名称
			if (null != fleet_name && !"".equals(fleet_name)) {
				sql.append("   AND TTC.COMPANY_S_NAME LIKE '%"
						+ fleet_name.trim() + "%'\n");
			}
			// 集团客户合同
			if (null != contract_no && !"".equals(contract_no)) {
				sql.append("   AND TTS.CONTRACT_NO LIKE '%"
						+ contract_no.trim() + "%'\n\n");
			}
		}
		// 上报日期
		if (null != startDate && !"".equals(startDate)) {
			sql.append("   AND TTS.SALES_DATE >= TO_DATE('" + startDate
					+ "','yyyy-MM-dd')\n");
		}
		if (null != endDate && !"".equals(endDate)) {
			sql.append("   AND TTS.SALES_DATE  <= (TO_DATE('" + endDate
					+ "','yyyy-MM-dd')+1)\n");
		}
		sql.append("AND TMV.LOCK_STATUS!=10241005");
		sql
				.append(" AND NOT EXISTS (SELECT 1 FROM TT_DEALER_ACTUAL_SALES_AUDIT TA WHERE TA.CTM_ID = TTC.CTM_ID AND TA.STATUS="
						+ Constant.SALES_INFO_CHANGE_STATUS_01 + ")\n");
		sql.append(" ORDER BY TTS.SALES_DATE DESC\n");
		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}

	/***
	 *实销信息上报:查询客户列表
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> getCustomerList(
			String dealerId, String customerName, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TTC.CTM_ID,\n");
		sql.append("       TTC.CTM_NAME,\n");
		sql.append("       TTC.SEX,\n");
		sql.append("       TTC.MAIN_PHONE,\n");
		sql.append("       TTC.OTHER_PHONE,\n");
		sql.append("       TO_CHAR(TTC.BIRTHDAY,'yyyy-MM-dd')BIRTHDAY,\n");
		sql.append("       TTC.IS_MARRIED\n");
		sql.append("  FROM TT_CUSTOMER TTC\n");
		sql.append(" WHERE TTC.DLR_COMPANY_ID IN\n");
		sql.append("       (SELECT TMD.COMPANY_ID\n");
		sql.append("          FROM TM_DEALER TMD\n");
		sql.append("         WHERE TMD.DEALER_ID IN (" + dealerId + "))\n");
		if (null != customerName && !"".equals(customerName)) {
			sql.append("       AND TTC.CTM_NAME LIKE '%" + customerName.trim()
					+ "%'\n");
		}
		sql.append("ORDER BY TTC.CTM_NAME\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}

	/***
	 *实销信息上报:查询集团客户列表
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> getFleetList(
			String fleet_name, Long dlrCompanyId, Long oemCompanyId, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT TEMP.FLEET_ID,\n");
		sql.append("       TEMP.FLEET_NAME,\n");  
		sql.append("       TEMP.FLEET_TYPE,\n");  
		sql.append("       TEMP.PURPOSE,\n");  
		sql.append("       TEMP.REGION,\n");  
		sql.append("       TEMP.MAIN_LINKMAN,\n");  
		sql.append("       TEMP.MAIN_PHONE,\n");  
		sql.append("       TEMP.TYPE\n");  
		sql.append("  FROM (SELECT TCP.PACT_ID FLEET_ID,\n");  
		sql.append("               TCP.PACT_NAME FLEET_NAME,\n");  
		sql.append("               NULL FLEET_TYPE,\n");  
		sql.append("               NULL PURPOSE,\n");  
		sql.append("               NULL REGION,\n");  
		sql.append("               NULL MAIN_LINKMAN,\n");  
		sql.append("               NULL MAIN_PHONE,\n");  
		sql.append("               'PACT' TYPE\n");  
		sql.append("          FROM TM_COMPANY_PACT TCP\n");  
		sql.append("         WHERE TCP.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("           AND TCP.OEM_COMPANY_ID = "+oemCompanyId+"\n");  
		if (null != fleet_name && !"".equals(fleet_name)) {
			sql.append("           AND TCP.PACT_NAME LIKE '%" + fleet_name.trim()
					+ "%'\n");
		}
		sql.append("        UNION\n");  
		sql.append("        SELECT DISTINCT TMF.FLEET_ID,\n");  
		sql.append("               TMF.FLEET_NAME,\n");  
		sql.append("               TMF.FLEET_TYPE,\n");  
		sql.append("               TMF.PURPOSE,\n");  
		sql.append("               TMF.REGION,\n");  
		sql.append("               TMF.MAIN_LINKMAN,\n");  
		sql.append("               TMF.MAIN_PHONE,\n");  
		sql.append("               'FLEET' TYPE\n");  
		sql.append("          FROM TM_FLEET TMF,TT_FLEET_CONTRACT A\n");  
		sql.append("         WHERE TMF.DLR_COMPANY_ID = "+dlrCompanyId+"\n");  
		sql.append("	       AND TMF.CON_STATUS = 1    AND A.FLEET_ID=TMF.FLEET_ID  AND A.STATUS="+Constant.FLEET_CON_STATUS_03+"\n");
		sql.append("           AND TMF.STATUS = "+Constant.FLEET_INFO_TYPE_03+"\n");  
		if (null != fleet_name && !"".equals(fleet_name)) {
			sql.append("           AND TMF.FLEET_NAME LIKE '%" + fleet_name.trim()
					+ "%'\n");
		}
		sql.append(") TEMP\n");  
		sql.append(" ORDER BY TEMP.TYPE DESC, TEMP.FLEET_ID ASC");
//		StringBuffer sql = new StringBuffer();
//
//		sql.append("SELECT TEMP.FLEET_ID,\n");
//		sql.append("       TEMP.FLEET_NAME,\n");  
//		sql.append("       TEMP.FLEET_TYPE,\n");  
//		sql.append("       TEMP.PURPOSE,\n");  
//		sql.append("       TEMP.REGION,\n");  
//		sql.append("       TEMP.MAIN_LINKMAN,\n");  
//		sql.append("       TEMP.MAIN_PHONE,\n");  
//		sql.append("       TEMP.TYPE\n");  
//		sql.append("  FROM (SELECT TCP.PACT_ID FLEET_ID,\n");  
//		sql.append("               TCP.PACT_NAME FLEET_NAME,\n");  
//		sql.append("               NULL FLEET_TYPE,\n");  
//		sql.append("               NULL PURPOSE,\n");  
//		sql.append("               NULL REGION,\n");  
//		sql.append("               NULL MAIN_LINKMAN,\n");  
//		sql.append("               NULL MAIN_PHONE,\n");  
//		sql.append("               'PACT' TYPE\n");  
//		sql.append("          FROM TM_COMPANY_PACT TCP\n");  
//		sql.append("         WHERE TCP.STATUS = "+Constant.STATUS_ENABLE+"\n");  
//		sql.append("           AND TCP.OEM_COMPANY_ID = "+oemCompanyId+"\n");  
//		if (null != fleet_name && !"".equals(fleet_name)) {
//			sql.append("           AND TCP.PACT_NAME LIKE '%" + fleet_name.trim()
//					+ "%'\n");
//		}
//		sql.append("        UNION\n");  
//		sql.append("        SELECT DISTINCT TMF.FLEET_ID,\n");  
//		sql.append("               TMF.FLEET_NAME,\n");  
//		sql.append("               TMF.FLEET_TYPE,\n");  
//		sql.append("               TMF.PURPOSE,\n");  
//		sql.append("               TMF.REGION,\n");  
//		sql.append("               TMF.MAIN_LINKMAN,\n");  
//		sql.append("               TMF.MAIN_PHONE,\n");  
//		sql.append("               'FLEET' TYPE\n");  
//		sql.append("          FROM TM_FLEET TMF,TT_FLEET_CONTRACT A\n");  
//		sql.append("         WHERE TMF.DLR_COMPANY_ID IN( SELECT TMD.COMPANY_ID FROM TM_DEALER TMD WHERE TMD.DEALER_ID IN(\n");  
//		sql.append(" SELECT VOD1.DEALER_ID\n");  
//		sql.append(" FROM VW_ORG_DEALER VOD1\n");  
//		sql.append(" WHERE VOD1.ROOT_DEALER_ID IN\n");  
//		sql.append(" (SELECT VOD.ROOT_DEALER_ID\n");  
//		sql.append(" FROM VW_ORG_DEALER VOD\n");  
//		sql.append(" WHERE VOD.DEALER_ID IN ( SELECT TMD.DEALER_ID FROM TM_DEALER TMD,TM_DEALER_BUSINESS_AREA TDBA WHERE TMD.DEALER_ID IN("+dealerIds+") AND TMD.DEALER_ID=TDBA.DEALER_ID AND TDBA.AREA_ID NOT IN(2010010100000004,2010010100000005,2010010100000006)))))\n");  
//		sql.append("	       AND TMF.CON_STATUS = 1    AND A.FLEET_ID=TMF.FLEET_ID  AND A.STATUS="+Constant.FLEET_CON_STATUS_03+"\n");
//		sql.append("           AND TMF.STATUS = "+Constant.FLEET_INFO_TYPE_03+"\n");  
//		if (null != fleet_name && !"".equals(fleet_name)) {
//			sql.append("           AND TMF.FLEET_NAME LIKE '%" + fleet_name.trim()
//					+ "%'\n");
//		}
//		sql.append(") TEMP\n");  
//		sql.append(" ORDER BY TEMP.TYPE DESC, TEMP.FLEET_ID ASC");

		
		/*sql.append("SELECT TMF.FLEET_ID,\n");
		sql.append("       TMF.FLEET_NAME, --客户姓名\n");
		sql.append("       TMF.FLEET_TYPE, --客户类型\n");
		sql.append("       TMF.PURPOSE, --购车用途\n");
		sql.append("       TMF.REGION, --区域\n");
		sql.append("       TMF.MAIN_LINKMAN, --主要联系人\n");
		sql.append("       TMF.MAIN_PHONE --主要联系人电话\n");
		sql.append("  FROM TM_FLEET TMF\n");
		sql.append(" WHERE TMF.DLR_COMPANY_ID =" + dlrCompanyId + "\n");
		if (null != fleet_name && !"".equals(fleet_name)) {
			sql.append("       AND TMF.FLEET_NAME LIKE '%" + fleet_name.trim()
					+ "%'\n");
		}
		sql.append(" ORDER BY TMF.CREATE_DATE DESC\n");*/

		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}

	/***
	 *实销信息上报:查询集团客户合同列表
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> getFleetContractList(
			String contract_no, String contract_amount, String startDate,
			String endDate, String fleet_id, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TTFC.CONTRACT_ID,\n");
		sql.append("       TTFC.CONTRACT_NO, --合同编号\n");
		sql.append("       TTFC.CONTRACT_AMOUNT, --合同数量\n");
		sql.append("       TTFC.DISCOUNT, --支持点位\n");
		sql
				.append("       TO_CHAR(TTFC.START_DATE, 'yyyy-MM-dd') START_DATE, --有效期起\n");
		sql
				.append("       TO_CHAR(TTFC.END_DATE, 'yyyy-MM-dd') END_DATE --有效期止\n");
		sql.append("  FROM TT_FLEET_CONTRACT TTFC\n");
		sql.append(" WHERE TTFC.FLEET_ID =" + fleet_id + "\n");
		if (null != contract_no && !"".equals(contract_no)) {
			sql.append("    AND TTFC.CONTRACT_NO LIKE '%" + contract_no
					+ "%'\n");
		}
		if (null != contract_amount && !"".equals(contract_amount)) {
			sql.append("    AND TTFC.CONTRACT_AMOUNT LIKE '%"
					+ contract_amount.trim() + "%'\n");
		}
		if (null != startDate && !"".equals(startDate)) {
			sql.append("    AND TTFC.START_DATE >= TO_DATE('" + startDate
					+ "','yyyy-MM-dd')\n");
		}
		if (null != endDate && !"".equals(endDate)) {
			sql.append("    AND TTFC.END_DATE <= TO_DATE('" + endDate
					+ "','yyyy-MM-dd')\n");
		}
		sql.append(" ORDER BY TTFC.CREATE_DATE DESC\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}
	
	/**
	 * 实销信息上报:查询大客户合同列表
	 * @param fleet_id
	 * @return
	 */
	public static List<Map<String, Object>> getFleetContractList(String fleet_id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TTFC.CONTRACT_ID,\n");
		sql.append("       TTFC.CONTRACT_NO, --合同编号\n");
		sql.append("       TTFC.CONTRACT_AMOUNT, --合同数量\n");
		sql.append("       TTFC.DISCOUNT, --支持点位\n");
		sql
				.append("       TO_CHAR(TTFC.START_DATE, 'yyyy-MM-dd') START_DATE, --有效期起\n");
		sql
				.append("       TO_CHAR(TTFC.END_DATE, 'yyyy-MM-dd') END_DATE --有效期止\n");
		sql.append("  FROM TT_FLEET_CONTRACT TTFC\n");
		sql.append(" WHERE TTFC.FLEET_ID =" + fleet_id + "\n");
		sql.append(" ORDER BY TTFC.CREATE_DATE DESC\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName());
	}

	/***
	 *实销信息上报:查询联系人列表(分页)
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static PageResult<Map<String, Object>> getLinkManList(
			String linkManName, String ctm_id, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TTL.LM_ID,\n");
		sql.append("       TTL.NAME,\n");
		sql.append("       TTL.MAIN_PHONE,\n");
		sql.append("       TTL.OTHER_PHONE,\n");
		sql.append("       TTL.CONTRACT_REASON\n");
		sql.append("  FROM TT_LINKMAN TTL\n");
		sql.append(" WHERE TTL.CTM_ID = " + ctm_id + "\n");
		if (null != linkManName && !"".equals(linkManName)) {
			sql.append("       AND TTL.NAME LIKE '%" + linkManName.trim()
					+ "%'\n");
		}
		sql.append(" ORDER BY TTL.NAME\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}

	/***
	 *实销信息上报:查询联系人列表
	 * 
	 * @param :
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getLink_List(Long oldCustomerId) {
		if (null != oldCustomerId && 0 != oldCustomerId) {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT TTL.LM_ID,\n");
			sql.append("       TTL.NAME,\n");
			sql.append("       TTL.MAIN_PHONE,\n");
			sql.append("       TTL.OTHER_PHONE,\n");
			sql.append("       TTL.CONTRACT_REASON\n");
			sql.append("  FROM TT_LINKMAN TTL\n");
			sql.append(" WHERE TTL.CTM_ID = " + oldCustomerId + "\n");
			sql.append(" ORDER BY TTL.NAME\n");
			return dao.pageQuery(sql.toString(), null, dao.getFunName());
		} else {
			return null;
		}

	}
	
	public static Map<String, Object> getSaleDLRInfo(String vhId) {
		StringBuffer sql = new StringBuffer("\n") ;
		
		sql.append("SELECT TMD.DEALER_CODE, TMD.DEALER_NAME, TO_CHAR(TTDAS.SALES_DATE,'yyyy-mm-dd') SALES_DATE\n");
		sql.append("  FROM TT_DEALER_ACTUAL_SALES TTDAS, TM_DEALER TMD\n");  
		sql.append(" WHERE TTDAS.DEALER_ID = TMD.DEALER_ID\n");  
		sql.append("   AND TTDAS.VEHICLE_ID = " + vhId + "\n");

		return dao.pageQueryMap(sql.toString(), null, dao.getFunName()) ;
	}

	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}

}
